﻿/**********************************弘石项目*******************************************
* 功能描述: 系统设置  机构设置:人员 培训，常用设置
* 作者:石锦涛
* 时间:2021-12-21
* 
**********************************弘石项目*******************************************/
using Dapper;
using HongStone.Domain.AttendanceManagement;
using HongStone.Domain.OperationsManagement;
using HongStone.Domain.SystemSetupModel.FrequentlyUsedDataModel;
using HongStone.Domain.SystemSetupModel.InstitutionSettingModel;
using HongStone.Dto;
using HongStone.Ince.IClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Snowflake.Core;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Z.Dapper.Plus;

namespace HongStone.Repository
{
    public class SystemManagementSJTDal
    {
        private readonly HongStoneContext _hongStoneContext;
        private readonly ILogger<SystemManagementSJTDal> _logger;

        private readonly IConfiguration _configuration;
        public SystemManagementSJTDal(HongStoneContext hongStoneContext, IConfiguration configuration, ILogger<SystemManagementSJTDal> logger)
        {
            _hongStoneContext = hongStoneContext;
            _configuration = configuration;
            _logger = logger;
            _logger.LogDebug(1, "NLog injected into HomeController");
        }


        /// <summary>
        /// 人员信息显示
        /// </summary>
        /// <param name="totalCount"></param>
        /// <param name="bum"></param>
        /// <param name="name"></param>
        /// <param name="state"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public List<StaffShowDto> Showstaff(out int totalCount,int? bum,string name,int? state,int pageindex=1,int pagesize=5)
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var sql = " select a.StaffId,a.StaffNumber,a.LoginName,a.StaffName,a.StaffTime,a.Sex,a.DeptId,b.DeptName,a.MajorId,a.RoleId,a.Telephone,a.Phone,a.Img,a.ManagementId,a.ProfessionalId,a.State,a.EntryTime,a.ResignationTime,a.DeptIds,a.[Order] from Staff a join Department b on a.DeptId = b.DeptId where 1=1 ";

                    if(bum>0)
                    {
                        sql += $" and a.DeptId='{bum}'";
                    }
                    if(!string.IsNullOrEmpty(name))
                    {
                        sql += " and StaffName like '%" + name + "%'";
                    }
                    if(state>0)
                    {
                        sql += $" and a.State = {state}";
                    }

                    var list = conn.Query<StaffShowDto>(sql);
                    totalCount = list.Count();
                    list = list.OrderBy(p => p.StaffId).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

                    return list.ToList();
                }

            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 人员信息添加
        /// </summary>
        /// <param name="staff"></param>
        /// <returns></returns>
        public int AddStaff(Staff staff)
        {
            try
            {
                _hongStoneContext.staffs.Add(staff);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 显示详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Staff ShowstaffDetails(int id)
        {
            try
            {
               return _hongStoneContext.staffs.FirstOrDefault(p => p.StaffId == id);
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 人员信息修改
        /// </summary>
        /// <param name="staffst"></param>
        /// <returns></returns>
        public int UpdateStaff(Staff staffst)
        {
            try
            {
                _hongStoneContext.Entry(staffst).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 人员批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int staffDate(string ids)
        {
            try
            {
                var number = ids.Split(',');
                foreach (var item in number)
                {
                    var id = Convert.ToInt32(item);
                    var list = _hongStoneContext.staffs.Find(id);
                    if(list!=null)
                    {
                        _hongStoneContext.staffs.Remove(list);
                    }
                }
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 培训信息列表
        /// </summary>
        /// <param name="bum"></param>
        /// <param name="xingzhi"></param>
        /// <param name="yeary"></param>
        /// <param name="month"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public ResultData ShowTrains(int? bum,string xingzhi,int? yeary,string month,string content)
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var sql = "  select a.TrainOneId,b.TrainMoreId,a.Character,a.Content,a.CreateTime,a.EndTime,b.StaffId,c.StaffName,b.DeptId,d.DeptName,b.EntryFee,b.AirTicket,b.Stay,b.Rest,b.Total,b.InTotal from TrainAndOne a join TrainAndMore b on a.TrainAndOneNumber = b.TrainAndMoreNumber join Staff c on b.StaffId = c.StaffId join Department d on b.DeptId = d.DeptId where 1 =1 ";
                    if(bum>0)
                    {
                        sql += $" and b.DeptId='{bum}' ";
                    }
                    if (!string.IsNullOrEmpty(xingzhi))
                    {
                        sql += $" and b.Character='{xingzhi}' ";
                    }
                    if(yeary>0)
                    {
                        sql += $" and DATENAME(YEAR,a.CreateTime)='{yeary}' ";
                    }
                    if(!string.IsNullOrEmpty(month))
                    {
                        sql += $" and SUBSTRING(CONVERT(Nvarchar(50),a.CreateTime),6,2)='{month}' ";
                    }
                    if(!string.IsNullOrEmpty(content))
                    {
                        sql += $" and a.Content like '%{content}%' ";
                    }

                    var query = conn.Query<ShowTrainDto>(sql).ToList();
                    return new ResultData { Code = 1,Data =query };
                }
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 培训添加
        /// </summary>
        /// <param name="showTrainDto"></param>
        /// <returns></returns>
        public ResultData AddTrains(ShowTrainDto showTrainDto)
        {
            try
            {
                using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL"))) {

                    var worker = new IdWorker(1, 1);
                    var id = worker.NextId().ToString();

                    var sql = $"insert TrainAndOne values('{id}','{showTrainDto.trainAndOne.Character}','{showTrainDto.trainAndOne.Content}','{showTrainDto.trainAndOne.CreateTime}','{showTrainDto.trainAndOne.EndTime}')";
                    var list = conn.Execute(sql);
                    foreach (var item in showTrainDto.trainAndMores)
                    {
                        var sqls = $"insert TrainAndMore values('{id}','{item.StaffId}','{item.DeptId}','{item.EntryFee}','{item.AirTicket}','{item.Stay}','{item.Rest}','{item.Total}','{item.InTotal}')";
                        list += conn.Execute(sqls);
                    }

                    return new ResultData { Code=1,Data=list};
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 显示人员
        /// </summary>
        /// <returns></returns>
        public List<StaffShowDto> Showren( int? bum)
        {
            try
            {
                using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var sql = " select a.StaffId,a.StaffNumber,a.LoginName,a.StaffName,a.StaffTime,a.Sex,a.DeptId,b.DeptName,a.MajorId,a.RoleId,a.Telephone,a.Phone,a.Img,a.ManagementId,a.ProfessionalId,a.State,a.EntryTime,a.ResignationTime,a.DeptIds,a.[Order] from Staff a join Department b on a.DeptId = b.DeptId where 1=1 ";

                    if (bum > 0)
                    {
                        sql += $" and a.DeptId='{bum}'";
                    }

                    var list = conn.Query<StaffShowDto>(sql).ToList();
                    return list;
                    
                }

            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 修改培训
        /// </summary>
        /// <param name="showTrainDto"></param>
        /// <returns></returns>
        public int Updateshuju(ShowTrainDto showTrainDto)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {



                var sql = $"update TrainAndOne set  '{showTrainDto.trainAndOne.Character}','{showTrainDto.trainAndOne.Content}','{showTrainDto.trainAndOne.CreateTime}','{showTrainDto.trainAndOne.EndTime}' where TrainAndOneNumber='{showTrainDto.trainAndOne.TrainAndOneNumber}'";
                var list = conn.Execute(sql);
                foreach (var item in showTrainDto.trainAndMores)
                {
                    var sqls = $"update TrainAndMore set  '{item.StaffId}','{item.DeptId}','{item.EntryFee}','{item.AirTicket}','{item.Stay}','{item.Rest}','{item.Total}','{item.InTotal}' where TrainAndMoreNumber='{item.TrainAndMoreNumber}'";
                    list += conn.Execute(sqls);
                }

                return list;
            }
        }


        /// <summary>
        /// 删除培训
        /// </summary>
        /// <param name="TrainOneId"></param>
        /// <returns></returns>
        public int Deteltesc(int TrainOneId)
        {
            try
            {
                var list= _hongStoneContext.trainAndOnes.Find(TrainOneId);
                _hongStoneContext.trainAndOnes.Remove(list);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 字典显示
        /// </summary>
        /// <returns></returns>
        public List<DictionariesDto> ShowDiction(out int totalCount,string names,int pageindex=1,int pagesize=5)
        {
            try
            {
                var query = from a in _hongStoneContext.dictionariesTables
                            where a.IsDelete == true
                            select new DictionariesDto
                            {
                                DictionariesId = a.DictionariesId,
                                DictionariesName = a.DictionariesName,
                                DictionariesClass = a.DictionariesClass,
                                DExplain = a.DExplain,
                                DFoundTime = a.DFoundTime,
                            };
                if(!string.IsNullOrEmpty(names))
                {
                    query = query.Where(p => p.DictionariesName.Contains(names));
                }
                totalCount = query.Count();
                
                return query.OrderBy(p=>p.DictionariesId).Skip((pageindex-1)*pagesize).Take(pagesize).ToList();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 字典添加
        /// </summary>
        /// <param name="dictionaries"></param>
        /// <returns></returns>
        public ResultData Diction(DictionariesTable dictionaries)
        {
            try
            {
                 _hongStoneContext.dictionariesTables.Add(dictionaries);

                return new ResultData { Code = _hongStoneContext.SaveChanges() };
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 字典修改
        /// </summary>
        /// <param name="dictionaries"></param>
        /// <returns></returns>
        public int Update(DictionariesTable dictionaries)
        {
            try
            {
                _hongStoneContext.Entry(dictionaries).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 字典 批量删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int DeteleteDict(string ids)
        {
            try
            {
                var id = ids.Split(',');
                foreach (var item in id)
                {
                    var list = Convert.ToInt32(item);
                    var shuju = _hongStoneContext.dictionariesTables.Find(list);
                    if(shuju!=null)
                    {
                        shuju.IsDelete = false;
                    }
                }
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 链接显示
        /// </summary>
        /// <returns></returns>
        public List<FastLink> ShowLink()
        {
            try
            {
                return _hongStoneContext.fastLinks.ToList();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 添加快捷链接
        /// </summary>
        /// <param name="fast"></param>
        /// <returns></returns>
        public int AddLinke(FastLink fast)
        {
            try
            {
                _hongStoneContext.fastLinks.Add(fast);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 删除链接
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteLink(int id)
        {
            try
            {
                var list = _hongStoneContext.fastLinks.Find(id);
                if(list!=null)
                {
                    _hongStoneContext.fastLinks.Remove(list);
                }
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 显示工资
        /// </summary>
        /// <returns></returns>
        public List<StaffSalary> staffwageshuju()
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var sql = "   select * from StaffSalary ";
                    var list = conn.Query<StaffSalary>(sql).ToList();
                    return list;

                }
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 导入 上传 Excel 工资
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int Uploading(List<StaffSalary> user)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var list = conn.BulkInsert(user);
                if (list != null)
                {
                    return 1;
                }
                else
                {
                    return 0;
                }
            }
        }

        /// <summary>
        /// 显示职称
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<PositionMessage> ShowPositions(string name)
        {
            try
            {
                if(!string.IsNullOrEmpty(name))
                {
                    return _hongStoneContext.positionMessages.Where(p => p.PositionName.Contains(name)).ToList();
                }
                return _hongStoneContext.positionMessages.ToList();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 职称添加
        /// </summary>
        /// <param name="position"></param>
        /// <returns></returns>
        public int AddPositions(PositionMessage position)
        {
            try
            {
                _hongStoneContext.positionMessages.Add(position);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 修改职称数据
        /// </summary>
        /// <param name="position"></param>
        /// <returns></returns>
        public int UpdatePositions(PositionMessage position)
        {
            try
            {
                _hongStoneContext.Entry(position).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 批量删除职称
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int DeletPositions(string ids)
        {
            var idc = ids.Split(',');
            foreach (var item in idc)
            {
                var list = Convert.ToInt32(item);
                var query = _hongStoneContext.positionMessages.Find(list);
                if(query!=null)
                {
                    _hongStoneContext.positionMessages.Remove(query);
                }
            }
            return _hongStoneContext.SaveChanges();
        }

        /// <summary>
        /// 显示项目奖金统计
        /// </summary>
        /// <returns></returns>
        public List<BonusCoefficient> ShowBonus()
        {
            try
            {
                return _hongStoneContext.bonusCoefficients.ToList();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 系数奖金修改
        /// </summary>
        /// <param name="bonus"></param>
        /// <returns></returns>
        public int UpdateBonus(List<BonusCoefficient> bonus)
        {
            try
            {
                using (var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = 0;
                    foreach (var item in bonus)
                    {
                         list = conn.Execute($"update BonusCoefficient set Proportion={item.Proportion} where  BonusId in ({item.BonusId})");
                    }
                    return list;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 人员信息显示
        /// </summary>
        /// <param name="totalCount"></param>
        /// <param name="bum"></param>
        /// <param name="names"></param>
        /// <param name="yeays"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
         
        
        public List<StaffShowDto> ShowRatkStaff(out int totalCount,int? bum,string names,string yeays,int pageindex=1,int pagesize=10)
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var sql = "select a.StaffId,SUBSTRING(CONVERT(nvarchar(20),a.EntryTime),1,4) as EntryTimes ,a.StaffId,a.LoginName,a.StaffName,b.DeptName,a.[Order] from Staff a join Department b on a.DeptId=b.DeptId  where 1 =1 ";

                    if(bum>0)
                    {
                        sql += $" and a.DeptId={bum}";
                    }
                    if(!string.IsNullOrEmpty(names))
                    {
                        sql += $" and a.StaffName like '%{names}%'";
                    }
                    if (!string.IsNullOrEmpty(yeays))
                    {
                        sql += $" and SUBSTRING(CONVERT(nvarchar(20),a.EntryTime),1,4)='{yeays}'";
                    }
                    var list = conn.Query<StaffShowDto>(sql);
                    totalCount = list.Count();
                    list= list.OrderBy(p=>p.StaffId).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

                    return list.ToList();
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 显示级别
        /// </summary>
        /// <returns></returns>
        public List<DictionariesTable> ShowDictions()
        {
            using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = "select * from DictionariesTable where DictionariesClass='s_leavel'";
                var list = conn.Query<DictionariesTable>(sql).ToList();
                return list;
            }
        }

        /// <summary>
        /// 修改级别
        /// </summary>
        /// <param name="zhi"></param>
        /// <param name="jibie"></param>
        /// <returns></returns>
        public int Updateshujib(int zhi,int jibie)
        {
            try
            {
                var id = _hongStoneContext.staffs.Find(zhi);
                id.Order = jibie;
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

    }
}
